package com.guo.service.impl;

import com.guo.mapper.BatchAddMapper;
import com.guo.model.User;
import com.guo.service.BatchService;
import com.guo.util.MsgBaseRtn;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.thymeleaf.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * @Auther Heart Guo
 * @Date 2022/03/08-10:53
 */
@Service
public class BatchServiceImpl implements BatchService {

    @Autowired
    private BatchAddMapper batchAddMapper;

    @Override
    public MsgBaseRtn batchAdd(MultipartFile file, HttpServletRequest request) {
        MsgBaseRtn info = dealExcelContent(file, "error_batch_add_text", request);
        if(info.getMsg() == "success" || info.getMsg() == "err"){
            info.setDetail("上传成功");
        }
        return info;
    }

    /**
     * 处理excel内容
     */
    @SneakyThrows
    private MsgBaseRtn dealExcelContent(MultipartFile file, String errorTextName, HttpServletRequest request){
        MsgBaseRtn info = new MsgBaseRtn();
        InputStream stream = file.getInputStream();
        Workbook workbook = null;
        //获取原始文件名
        String originalFilename = file.getOriginalFilename();
        //判断文件类型
        if(originalFilename.endsWith("xlsx")){
            workbook = new XSSFWorkbook(stream);
        } else if(originalFilename.endsWith("xls")){
            workbook = new HSSFWorkbook(stream);
        } else {
            info.setErrorMessage("文件格式不正确");
            return info;
        }

        StringBuilder errorText = new StringBuilder();
        //读取excel表
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++){
            Sheet sheet = workbook.getSheetAt(numSheet);
            if(sheet == null){
                continue;
            }
            //循环行row
            int lastRowNum = sheet.getLastRowNum(); //获取最后一行
            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++){
                if(rowNum == 0){
                    continue;
                }
                Row row = sheet.getRow(rowNum);
                if(row != null){
                    //保存此行的数据
                    saveValue(row, errorText, rowNum, originalFilename.substring(0,originalFilename.indexOf(".")));
                }
            }
            break;
        }

        //将错误信息放到session中
        request.getSession().setAttribute(errorTextName,errorText.toString());

        if(workbook instanceof XSSFWorkbook){
            workbook.close();
        }
        if(errorText == null || "".equals(errorText.toString())){
            info.setMsg("success");
        }else
            info.setMsg("err");
        stream.close();

        return info;
    }

    /**
     * 插入数据
     */
    private void saveValue(Row row, StringBuilder errorText, Integer rowNUm, String fileName){

        //从方法中把包含行信息的map取出
        Map<String, String> map = validateParamForBatchAddValue(row, errorText, rowNUm, fileName);
        if(map == null || map.isEmpty()){
            return;
        }

        //取出map中的所有参数
        String userCode = map.get("userCode");
        String userName = map.get("userName");
        String age = map.get("age");
        String sex = map.get("sex");

        //创建对象接收参数
        User user = new User();
        user.setUserCode(userCode);
        user.setUserName(userName);
        user.setAge(age);
        user.setSex(sex);

        //存入数据库
        batchAddMapper.insert(user);
    }

    /**
     * 批量添加，参数校验
     */
    private Map<String, String> validateParamForBatchAddValue(Row row, StringBuilder errorText, int rowNum, String fileName){
        //获取一行中，每个单元格中的数据
        Cell userCode = row.getCell(0);
        Cell userName = row.getCell(1);
        Cell age = row.getCell(2);
        Cell sex = row.getCell(3);

        Map<String, String> map = new HashMap<>();

        //校验是否已经重复
        if(!queryByCode(userCode.toString())){
            errorText.append("文件《" + fileName +"》第 "+ rowNum + " 行，数据已存在\r\n");
            return map;
        }

        //校验每个单元格中的数据是否为空
        if(userCode == null || StringUtils.isEmpty(userCode.toString().trim())){
            errorText.append("文件《" + fileName +"》第 "+ rowNum + " 行，用户编号为空\r\n");
            return map;
        }
        if(userName == null || StringUtils.isEmpty(userName.toString().trim())){
            errorText.append("文件《" + fileName + "》第 " + rowNum + " 行，用户名为空\r\n");
            return map;
        }
        if(age == null || StringUtils.isEmpty(age.toString().trim())){
            errorText.append("文件《" + fileName + "》第 " + rowNum + " 行，年龄为空\r\n");
            return map;
        }
        if(sex == null || StringUtils.isEmpty(sex.toString().trim())){
            errorText.append("文件《" + fileName + "》第 " + rowNum + " 行，性别为空\r\n");
            return map;
        }

        //将格式转换为文本格式
        userCode.setCellType(XSSFCell.CELL_TYPE_STRING);
        userName.setCellType(XSSFCell.CELL_TYPE_STRING);
        age.setCellType(XSSFCell.CELL_TYPE_STRING);
        sex.setCellType(XSSFCell.CELL_TYPE_STRING);

        map.put("userCode", userCode.toString());
        map.put("userName", userName.toString());
        map.put("age", age.toString());
        map.put("sex", sex.toString());

        return map;
    }

    /**
     * 根据用户编号查询，用于判断是否有重复
     * @param userCode
     * @return 有：true；无：false
     */
    public boolean queryByCode(String userCode){
//        User user = batchAddMapper.queryByCode(userCode);
        if(batchAddMapper.queryByCode(userCode) == null){
            return false;
        }else
        return true;
    }
}
